Menu
I hope you must have face the question during interview to find database size. As an Oracle DBA you may face the requirement to get the current database size. In this example we will get to know the actual size of the database. Database size may vary depend on utilization, Free space hence we need to.
PurposeThis tutorial covers the following:. Creating a CONTEXT Index andQuerying with CONTAINS operator. Creating a CTXCAT Index andQuerying with CATSEARCHoperator. Creating a CTXRULE Index andQuerying with MATCHES operatorTime to CompleteApproximately 1 hourIntroductionAn Oracle Text index is an Oracle Database domain index. To buildyour query application, you can create an index of type CONTEXTwith a mixture of text and structured data columns, and query itwith the CONTAINS operator.You create an index from a populated text table. In a queryapplication, the table must contain the text or pointers to wherethe text is stored.
Text is usually a collection of documents, butcan also be small text fragments.You can use the CTXCAT indextype when your application relies heavily on mixed queries tosearch small documents or descriptive text fragments based onrelated criteria such as dates or prices. Query this index withthe CATSEARCH operator.You can create an index of type CTXRULEto build a document classification application using simple orrule-based classification.The benefits of a creating an Oracle Text index include fastresponse time for text queries with the CONTAINS,CATSEARCH, and MATCHESOracle Text operators. With Oracle Text, you can create indexes ofseveral types using CREATE INDEX.Index TypeDescriptionQuery OperatorNotesCONTEXTUse this index to build a text retrieval application whenyour text consists of large coherent documents.You can index documents of different formats such as MSWord, HTML or plain text.You can customize the index in a variety of ways.
This indextype requires CTXDDL.SYNCINDEXafter DML on base table.CONTAINS Grammar iscalled the CONTEXT grammar,which supports a rich set of operations.The CTXCAT grammar can beused with query templating.Supports all documents services and query services.Supports indexing of partitioned text tables. Supports FILTER BY and ORDERBY clauses of CREATEINDEX to also index structured column values formore efficient processing of mixed queries.CTXCATUse this index type for better mixed query performance.Typically, with this index type, you index small documentsor text fragments.Other columns in the base table, such as item names, prices,and descriptions can be included in the index to improvemixed query performance. This index type is transactional,automatically updating itself after DML to base table.
No CTXDDL.SYNCINDEX isnecessary.CATSEARCH Grammar iscalled CTXCAT, whichsupports logical operations, phrase queries, andwildcarding. The CONTEXTgrammar can be used with query templating. Theme querying issupported.This index is larger and takes longer to build than a CONTEXT index. The size of a CTXCAT index is related to thetotal amount of text to be indexed, the number of indexes inthe index set, and the number of columns indexed. Carefullyconsider your queries and your resources before addingindexes to the index set. The CTXCATindex does not support index partitioning, documentsservices (highlighting, markup, themes, and gists) or queryservices (explain, query feedback, and browse words.)CTXRULEUse CTXRULE index tobuild a document classification or routing application. Thisindex is created on a table of queries, where the queriesdefine the classification or routing criteria.MATCHESSingle documents (plain text, HTML, or XML) can beclassified using the MATCHESoperator, which turns a document into a set of queries andfinds the matching rows in the CTXRULEindex.PrerequisitesBefore starting this tutorial, you should:.
PerformOBE.Creating a CONTEXT Index andQuerying with CONTAINS operator.Run the SELECT statementto query the tables from the USERTABLESdictionary view.SELECT tablename FROM usertables;Note: You can see the QUICKtable you created, but you can also see that thereare four additional tables, with names based on ourindexname.Query the $Itable.SELECT tokentext FROM dr$quickindex$i;Note: Here, you can see the words from the'the cat sat on the mat' document. But 'the'and 'on' did not appear.
They did not appear becausethey are called the stop words - words that areconsidered not useful for searching, so they are notindexed. You can customize the list of stopwords foran index. The $Itable is sometimes known as the 'word list' table.It contains a list of words, each associated withsome binary data (in the tokeninfo column) whichrepresents the locations of each word within thedocument set.Synchronizing a Text Index. Synchronizing the index involves processing all pendingupdates, inserts, and deletes to the base table. You can dothis in PL/SQL with the CTXDDL.SYNCINDEXprocedure.Add a new row to the QUICKtable using the following INSERTstatement.INSERT INTO quick VALUES ('the cat sat on thedog');.Query the $Itable.SELECT tokentext FROM dr$quickindex$i;Note: The word 'dog' did not appear becausethe index has not yet been synchronized.Execute the following PL/SQL procedure to sync anindex.EXECUTE ctxddl.syncindex('QUICKINDEX');.Query the $Itable.SELECT tokentext FROM dr$quickindex$i;Note: The word 'dog' appeared in our indexbecause the index has been synced.
But there'ssomething else to notice here - do you see thatthere are multiple rows for 'CAT' and 'SAT'? This isindex fragmentation - due to updates, our index isno longer in an optimal state. You can fix this byrunning another PL/SQL procedure optimizeindex.Optimizing an Index. Frequent index synchronization can fragment your CONTEXTindex. Index fragmentation can adversely affect queryresponse time. You can optimize your CONTEXTindex to reduce fragmentation and index size and soimprove query performance.This comes in various modes, which are beyond the scope ofthis tutorial, but for now we will just run it in FULLmode.Execute the following PL/SQL procedure to optimize anindex.EXECUTE ctxddl.optimizeindex('QUICKINDEX','FULL');.Query the $Itable.SELECT tokentext FROM dr$quickindex$i;Note: The index is now in an optimal state -best for space usage and for query performance.
Themore frequently you sync and index, the morefragmented it will become and the longer it willtake to optimize. So any large scale Oracle Textimplementation will have to plan how often to syncindexes, and how frequently to run optimize.A commonscenario is to sync indexes every five minutes, andoptimize them daily.Querying with CONTAINSoperator. When you create an index of type CONTEXT,you must use the CONTAINSoperator to enter your query.Add two more rows to your QUICKtable using the followingINSERT statements:INSERT INTO quick VALUES ('the cat sat on thecat');INSERT INTO quick VALUES ('the dog sat on the cat');.Enter the following code to synchronize your index:EXECUTE ctxddl.syncindex('QUICKINDEX');.Run the following SELECTstatement, specify the query in the WHEREclause with the CONTAINSoperator:SELECT text FROM quick WHERE CONTAINS (text, 'catsat') 0;Note: cat sat' is a phrase search.
Bothwords must appear together in the right order. Here,you did not find 'the dog sat on the cat'. If youwant to find the words wherever they are we must usethe AND operator (which can also be expressed as'&'). Here, the text 'the dog sat on the cat'did not appear.Run the following SELECTstatement, specify the query in the WHEREclause with the CONTAINSoperator and the AND operator:SELECT text FROM quick WHERE CONTAINS (text, 'catAND sat') 0;The SCORE Operator. CONTAINS returns arelevance score for every row selected. You obtain thisscore with the SCOREoperator.
To create a CTXRULEindex, perform the following steps:.Open a SQL Worksheet using myuserconn and enter thefollowing code to create a table called myqueriesto hold the category name and query text.
Earlier I had ran puzzle where I asked question regarding size of index table for each index in database over here. I had received good amount answers and I had blogged about that here.
As a comment to that blog I have received another very interesting comment and that provides near accurate answers to original question. Many thanks to for providing wonderful solution.SELECTOBJECTNAME ( i. OBJECTID ) AS TableName,i.name AS IndexName,i.indexid AS IndexID,8.
SUM ( a.usedpages ) AS 'Indexsize(KB)'FROM sys.indexes AS iJOIN sys.partitions AS p ON p. OBJECTID = i. OBJECTID AND p.indexid = i.indexidJOIN sys.allocationunits AS a ON a.containerid = p.partitionidGROUP BY i. OBJECTID, i.indexid, i.nameORDER BY OBJECTNAME ( i. OBJECTID ), i.indexidLet me know if you have any better script for the same.Reference: Pinal Dave ( ). Pinal,Here is one I wrote.
Hi Sir,It is now giving absolute correct result.i made that index non clustered and now perfect result.Amazing hands on SQL SERVER Sir.HATS OFF to you.i have drawn conclusion that if there is a clustered index then size of index would be displayed index size + table sizebut if the index is non clustered then size of index will be separate from table size andsir note that not even a single insert of mine is overflowing the page size that is 8060 bytes so would be in a single partition and no furhter allocation.but why sizes are consolidated and separated?? See as far as i know then it has to do something with mix and uniform extents or else concept is there??My second perception on this is (just what i know)– Clustered index consist a data rows in a leaf node thats why its size is consolidated with table size– non cluster index contains a row locator at leaf node thats why index structure is totally separated from table data structuremy question is like why clustered index size is calculated like=table size+index sizeand non clustered is like only table size different and index size different???THANKS AND REGARDSKamesh Shah. It’s being a long time since the creation for this post but I wanted to share my script too:WITH CteIndexAS(SELECTreservedpages = (reservedpagecount),usedpages = (usedpagecount),pages = (CASEWHEN (s.indexid pages THEN CASE WHEN ct.indexid. Hello Pinal, thanks for the good script.I have developed another script that gives me a little bit different values for index Size.If I compare the index size values returned by SSMS GUI (in terms of pages) my script is more precise.
![How to check index size in oracle database How to check index size in oracle database](/uploads/1/2/5/3/125357269/398794575.jpg)
![Oracle Oracle](/uploads/1/2/5/3/125357269/413670117.png)
I simply use the sys.dmdbindexphysicalstat (it returns the size already) and NOT the sys.allocationunits.select objectname(Ix.objectid), Ix.name, ix.indexid, ps.pagecountfrom sys.dmdbindexphysicalstats(dbid,null,null,NULL,’DETAILED’) PSINNER JOIN sys.indexes IXON ix.objectid = PS.objectidAND ix.indexid = Ps.indexidorder by 4 descI would like to have your opinion.ThanksSaverio. Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 23 Pluralsight courses and has written over 4700 articles on the database technology on his blog at a Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.For any send an email at [email protected] is also a and.Nupur Dave is a social media enthusiast and an independent consultant.