Solved

select query performance

Posted on 2008-10-29
9
588 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 shows how to recover a database from a user managed backup

744 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

12 Experts available now in Live!

Get 1:1 Help Now