Solved

select query performance

Posted on 2008-10-29
9
589 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to get sorting resultset 15 68
dates - loop 12 57
Oracle Next Available Number 2 32
Need help with fine tuning the windows batch script to change password 9 20
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now