- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Index - Urgent
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-15-2004 06:58 AM
тАО09-15-2004 06:58 AM
Index - Urgent
This is my query,
SELECT /*+index(TPCTR_DAILY_HIST XPCTR_DHIST) */
A.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC,Sum(NVL(A.DAILY_BALANCE,0)),Sum(NVL(A.NUM_OF_ACCTS,0))
FROM
TPCTR_DAILY_HIST A ,
PRODUCT_MKTPROD B ,
TALL_PCTRS C
WHERE
A.PROD_CODE=B.PROD_CODE AND
A.PCTR_NUM = C.PCTR_NUM AND
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC
ORDER BY
a.PROD_CODE,B.PROD_NAME,A.PCTR_NUM,C.PCTR_DESC
I have index on TPCTR_DAILY_HIST table's 6 fields, which includes COMPANY_CODE,BUSGROUP,
PCTR_NUM,PROD_CODE,AS_OF_DATE and one more which i dont need in the query.
Above Query returns result in 9 seconds...
however if i change the date condition to
A.AS_OF_DATE >='01-AUG-2004' AND
A.AS_OF_DATE <='31-AUG-2004'
It takes forever ..query doesnt return at all.
What could be the issue. I have few million records in TPCTR_DAILY_HIST and other 2 are reference tables with recors in few hundreds.
I use Oracle8i Enterprise Edition Release 8.1.6.0.0.
Thanks
Jil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2004 08:16 AM
тАО09-15-2004 08:16 AM
Re: Index - Urgent
A.AS_OF_DATE >='01-AUG-2004' AND
A.AS_OF_DATE <='31-AUG-2004'
use the BETWEEN operator, like so:
A.AS_OF_DATE BETWEEN '01-AUG-2004' AND '31-AUG-2004'
You could also try placing an index specifically on the AS_OF_DATE field and removing your index hint from the query.
Take a look at the tkprof utility and use it to perform SQL tracing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2004 04:21 PM
тАО09-15-2004 04:21 PM
Re: Index - Urgent
can you please post the output of the execution plan for each case?
e.g.
sqlplus
set autotrace traceonly
also, confirm if your optimiser settings:
show parameter optimizer_mode
and use "between" as mentioned in the previous post.
and finally try not to use implicit conversion, i.e.
instead of '01-AUG-2004', use to_date('01-AUG-2004','DD-MON-YYYY')
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2004 04:45 AM
тАО09-16-2004 04:45 AM
Re: Index - Urgent
Thanks for the support.
I made the changes you mentioned...
now query is coming back after an hour.
I'm new to oracle , so i dont know how to
get the execution plan..can we to it in Toad ? i tried in SQLplus as u mentioned..but i dont know how to interpret it.
Thanks
JIL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2004 05:07 AM
тАО09-16-2004 05:07 AM
Re: Index - Urgent
to get the execution you need a plan_table put the output of explain plan into the table and then use a select to show the result. It is described in the Oracle docs.
You should also consider patching your oracle since you have an unpatched version.
greetings,
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-16-2004 05:16 AM
тАО09-16-2004 05:16 AM
Re: Index - Urgent
can you please give the output of
desc TPCTR_DAILY_HIST
desc PRODUCT_MKTPROD
desc TALL_PCTRS
and how many rows each of these tables has.
Very important is the sequence of the fields in the index. It might be if the sequence does not fit, that the index is not used at all !
Second: You do aggregate functions on a join. This is expensive, because the join is build first and then goes the group and sort.
Assumption: The complete number result can be calculated on table A alone, as B and C are only lookup tables for IDs.
If this is true, try if this fits your need:
SELECT
A.PROD_CODE,A.PCTR_NUM,Sum(NVL(A.DAILY_BALANCE,0)),Sum(NVL(A.NUM_OF_ACCTS,0))
FROM
TPCTR_DAILY_HIST A
WHERE
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,A.PCTR_NUM
ORDER BY
A.PROD_CODE,A.PCTR_NUM
If that is what you like to have, use this as a subselect and join later. The result will have a whole lot less number of rows and should be very easy to join to the other tables afterwards. I try it here, and I hope it fits, but be sure to check, as I do not have the data ... :-)
Watch it, I shifted your B and C to C and D, because I needed B for the subselect.
select B.A_PCODE,
C.PROD_NAME,
B.A_PCTRNUM,
D.PCTR_DESC,
B.A_DBAL,
B.NUMACC
from
(
SELECT
A.PROD_CODE as A_PCODE,
A.PCTR_NUM as A_PCTRNUM,
Sum(NVL(A.DAILY_BALANCE,0)) as A_DBAL,
Sum(NVL(A.NUM_OF_ACCTS,0)) as A_NUMACC
FROM
TPCTR_DAILY_HIST A
WHERE
A.BUS_GROUP='RET' AND
A.COMPANY_CODE='0102' AND
A.AS_OF_DATE ='01-AUG-2004'
Group by
A.PROD_CODE,A.PCTR_NUM
ORDER BY
A.PROD_CODE,A.PCTR_NUM
) B,
PRODUCT_MKTPROD C ,
TALL_PCTRS D
WHERE
B.A_PCODE = C.PROD_CODE AND
B.A_PCTRNUM = D.PCTR_NUM
To support the subselect query, the index on table A should have
A.BUS_GROUP,A.COMPANY_CODE,A.AS_OF_DATE
To support the join of the other tables,
you should have
C.PROD_CODE on table C
D.PCTR_NUM on table D
Hope this helps
Volker