- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Database query question
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 12:47 AM
тАО09-26-2003 12:47 AM
thanks.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 12:55 AM
тАО09-26-2003 12:55 AM
Re: Database query question
CREATE INDEX emp_name_index ON emp ( UPPER(ename)) ;
I think Oracle Enterprise Edition is required for this feature.
Rgds,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 02:11 AM
тАО09-26-2003 02:11 AM
Re: Database query question
A few databases would allow you to use mixed-case data in the column but the index would be allowed to ignore case for comparisons so that you got the best of both worlds --- but this doesn't apply to you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 02:17 AM
тАО09-26-2003 02:17 AM
Re: Database query question
select *
from table1 a, table2 b
where a.unit_cd = '10a'
and a.unit_cd = b.cost_code;
I'll have to keep looking or see if the developers want to add a 2nd column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 02:24 AM
тАО09-26-2003 02:24 AM
Re: Database query question
Waste of space !
You probably need a trigger to populate the extra column and probably and index 2 for performance.
Could you force the index with SQL Hints ?
Rgds,
Jean-Luc
PS : nice salad on the head... I suppose the buggy pb at the moment
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 02:29 AM
тАО09-26-2003 02:29 AM
Re: Database query question
did you try your query with :
select *
from table1 a, table2 b
where Upper(a.unit_cd) = '10A'
and Upper(a.unit_cd) = Upper(b.cost_code);
with
CREATE INDEX TAB1IDX ON TABLE1 (UPPER(unit_cd));
and
CREATE INDEX TAB2IDX ON TABLE2 (UPPER(cost_code));
What is the output of the explain plan ?
Rgds,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 03:22 AM
тАО09-26-2003 03:22 AM
Re: Database query question
select *
from table1 a, table2 b
where a.cost_code = b.unit_cd
and b.unit_cd = '10a';
1. UPPER not on any index or in query, oracle users both indexes on both columns
2. Add UPPER to b.unit_cd in query and it uses both indexes, but since I need to upper cost_code this doesn't help. Add UPPER to cost_code with or without UPPER on unit_cd and oracle doesn't user cost_code's index but does user unit_cd's index.
3. Add UPPER to cost_code index. Oracle uses unit_cd's index but not cost_codes index. Add UPPER to the query while UPPER is also in the cost_code index and oracle still only uses unit_cd's index.
4. Add upper to cost_code and unit_cd's indexes and oracle never uses any of the indexes with or without UPPER in the query.
For some reason oracle is just using unit_cd's indexes. I took out the unit_cd = '10a' and oracle still uses the unit_cd index.
Any other suggestions? It just doesn't seem to be working the way it should.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 03:36 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-26-2003 06:34 AM
тАО09-26-2003 06:34 AM
Re: Database query question
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-28-2003 03:39 AM
тАО09-28-2003 03:39 AM
Re: Database query question
*
if you are using the CBO, also make sure your statistics are up to date.
*
You may wish to re-compute them using:
++++++++++++++++++++++++++++++++++++++
analyze table
for table
for all indexed columns
for all indexes;
++++++++++++++++++++++++++++++++++++++
*
Hope this helps!
*
regards
Yogeeraj