Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

select query performance

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
Pradeep_Tiwari
Asked:
Pradeep_Tiwari
  • 4
  • 3
1 Solution
 
MarkusIdCommented:
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
 
Pradeep_TiwariAuthor Commented:
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
 
MarkusIdCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Pradeep_TiwariAuthor Commented:
startdate and enddate belongs to table XBrlTickerDetail.
give proper solution for this problem.
i am not able to send explain plan.
0
 
MarkusIdCommented:
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
 
Pradeep_TiwariAuthor Commented:
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
 
MarkusIdCommented:
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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now