Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news.tele.dk!news.tele.dk!small.news.tele.dk!fu-berlin.de!newsoffice.de!not-for-mail From: Andreas Mosmann Newsgroups: comp.databases.oracle.misc Subject: Re: O9i: general index question Date: Fri, 30 Mar 2007 09:42:34 +0200 Organization: Newsoffice.de - http://www.newsoffice.de Lines: 74 Sender: Andreas Mosmann Message-ID: <1175240554.24@user.newsoffice.de> References: <1175065531.64@user.newsoffice.de> <1175093787.522082.132610@d57g2000hsg.googlegroups.com> <1175097441.35@user.newsoffice.de> <3v7l03hnh3bgc08tmhkj46u3653m1a8mpo@4ax.com> <1175113203.799657.105820@y66g2000hsf.googlegroups.com> <1175157630.21@user.newsoffice.de> <1175177750.58@user.newsoffice.de> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Trace: localhost.localdomain 1175240555 9498 127.0.0.1 (30 Mar 2007 07:42:35 GMT) X-Complaints-To: usenet@localhost.localdomain NNTP-Posting-Date: Fri, 30 Mar 2007 07:42:35 +0000 (UTC) User-Agent: Newsoffice.de - based on NewsPortal X-Abuse-Management: 0sLLl6HbyJjax8TM5s3VmM+ZVXepmWNVY2FVsM6jQGeaYm1VaGpaoqmrU2hVY2BrmqY= Xref: usenetserver.com comp.databases.oracle.misc:246712 X-Received-Date: Fri, 30 Mar 2007 02:42:36 EST (text.usenetserver.com) sybrandb@hccnet.nl schrieb am 29.03.2007 in : > Yours is a typical case of Compulsive Index Disorder. and how to order? Or did I misunderstand you? > If you have a small table, one single index look up will cost > 1 read for the index header > 1 read for the index leaf block > 1 read for the data block > At least 3 different reads. > Even with db_file_multiblock_read_count set to 8, Oracle can read the > entire table using 1 scattered read, opposed to 3 sequential reads. this is surely correct for the small table, but not for the big one joined. That is why I did not try to build an index on the small table. but Oracle seems to need it for its CBO. > Do you want to make your application dead slow and unscalable? No, exactly this I wanted to change and had no more idea how to. > Apart from that you should use dbms_xplan.display to format explain > plan results. They look neater and provide more info. Thank you, nice hint. the actual execution plan displayed by dbms_xplan.display is ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 37266 | 12M| | | 3368 | | 1 | SORT ORDER BY | | 37266 | 12M| 25M| | 3368 | | 2 | HASH JOIN | | 37266 | 12M| | | 1518 | | 3 | TABLE ACCESS FULL | TSNUTZER | 127 | 2286 | | | 2 | | 4 | HASH JOIN | | 37271 | 11M| | | 1514 | | 5 | TABLE ACCESS FULL | TZCODES | 387 | 8514 | | | 2 | | 6 | HASH JOIN | | 37272 | 10M| | | 1509 | | 7 | TABLE ACCESS FULL | TSINSTITUTIONEN | 79 | 1501 | | | 2 | | 8 | HASH JOIN | | 37272 | 9M| | | 1505 | | 9 | TABLE ACCESS FULL | TSINSTITUTIONEN | 79 | 1501 | | | 2 | | 10 | HASH JOIN | | 37273 | 9536K| | | 1501 | | 11 | TABLE ACCESS FULL| TBNEBENANLAGEN | 1 | 70 | | | 2 | | 12 | TABLE ACCESS FULL| TBBAEUME | 447K| 81M| | | 1487 | ------------------------------------------------------------------------------------ > I would also like to stress that queries without the Sqlserver JOIN > junk are much more readable. This is one reason why you aren't getting > responses, no experienced Oracle developer is using that crap. Sorry, I did not know this. But my first and still unanswered question is a general one: What way I have to go to find out which composition of query/index give results in the fastest time? If I have an existing query and table situation, how to create matching indexes? Where can I read about it? I can not imagine that all the experienced oracle developers only work on try and error. if you have f.e. 6 columns mentioned in JOIN, WHERE an maybe GROUP BY clause there are 720 possibilities. Thanks for your answer Andreas Mosmann -- wenn email, dann AndreasMosmann web de