Solved

select query performance

Posted on 2008-10-29
9
594 Views
Last Modified: 2013-12-07
i am using oracle 10gR2 database, when i am going to execute one query my database take much more time for output result.How to increase performane of these queries.
Query is like:
SELECT a.elementid,D.ELEMENTORDER,LabelText AS ElementLabel  ,Amount as Amount0 ,0 as Amount1 ,0 as Amount2 ,
0 as Amount3 ,0 as Amount4 ,0 as Amount5
FROM XBrlTickerDetail  A, XBrlTmpXsdDet  B, XBrlTickerLab  C ,XBRLTICKERPRE  D
WHERE A.ticker=b.ticker And a.period=b.period And a.elementPropertyName=b.elementID
And A.Ticker=C.Ticker And A.Period=C.Period And  A.elementPropertyName=C.LabelId
And A.role='http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'
And A.ticker='edgr' and A.period='20071231'
AND A.TICKER=D.TICKER AND A.PERIOD=D.PERIOD AND A.ROLE=D.ROLE AND A.ELEMENTID=D.ELEMENTID
and TO_CHAR(startdate,'MM/DD/YYYY')||'-'||TO_CHAR(enddate,'MM/DD/YYYY')='12/31/2006-12/31/2006'
0
Comment
Question by:Pradeep_Tiwari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22829289
As it depends on a lot of factors, it would be nice to know, how many records are in each of these tables and which indexes there are. Furthermore, could you please post an explain-plan? That would make it much easier.
0
 

Author Comment

by:Pradeep_Tiwari
ID: 22829915
Each tables have 5000 to 10000 records.
indexes are:
table: XBrlTickerDetail: Index:ROLE, PERIOD, ELEMENTID, TICKER
table:XBrlTmpXsdDet: Index:PERIOD, ELEMENTID, TICKER
table:XBrlTickerLab: Index: TICKER, LABELID, PERIOD
table:XBRLTICKERPRE: Index:PERIOD, ELEMENTID, ROLE, TICKER
Explain Plan:
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22830239
I'm afraid, the explain plan is missing in your last post.

Which table do startdate and enddate belong to?
I would use  XBrlTickerDetail as the driving table (so it should be the last table in the from-clause), although the optimizer probably does use it this way anyway (as long as the table statistics have been gathered not too long ago).
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Pradeep_Tiwari
ID: 22838517
startdate and enddate belongs to table XBrlTickerDetail.
give proper solution for this problem.
i am not able to send explain plan.
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22838692
Hi,

You might try this one (it''s a bit hard if I don't know the current approach of the database):


SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.labelid
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 
       'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'

Open in new window

0
 

Author Comment

by:Pradeep_Tiwari
ID: 22839452
It's showing Errors:

Error starting at line 1 in command:
SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.labelid
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'
 
Error at Command Line:8 Column:21
Error report:
SQL Error: ORA-00904: "A"."LABELID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 500 total points
ID: 22839466
Oh, sorry, it should have been B.Elementid = A.elementPropertyName in line 8:
SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.elementPropertyName
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 
       'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question