0

Do clustered indexes have a separate index file or is it the table itself? I read from my textbook that clustered indexes change the physical ordering of the actual file. So does it need a separate file? Kindly provide a generic answer if possible, as this is from my texbook which states this topic as an abstract concept and not specific to dbms. Textbook referred:Database management systems by raghu ramakrishnan. RDBMS: Postgresql Thanks to everyone who tries to help :)

sumanth
  • 135
  • 1
  • 10
  • 1
    Please ask 1 specific researched non-duplicate question per post. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS Everything about indexes depends on the product. Why are you asking this? If you're in a textbook, which one & where are you 1st stuck? – philipxy Sep 29 '20 at 04:13
  • 1
    What **RDBMDS** is this for ?? First of all - not all RDBMS even have the concept of a "clustered index" - and if they do, most likely, not all RDBMS will treat these things exactly the same (since this is vendor-specific and not covered by any ANSI/ISO SQL standard...) – marc_s Sep 29 '20 at 04:44
  • 1
    You're **still** not telling us which **concrete** RDBMS this is for - which is **important** before we can even begin to answer this question! – marc_s Sep 29 '20 at 04:52
  • There's no question here. "this topic as an abstract concept and not specific to dbms" A particular system will implement functionality how implements it. – philipxy Sep 29 '20 at 04:52
  • @philipxy fixed . The textbook mentioned it as an abstract concept. So i asked it generically – sumanth Sep 29 '20 at 05:03
  • @marc_s postgres – sumanth Sep 29 '20 at 05:03
  • @philipxy, i did the research, i read the textbook, googled it, but everywhere it's mentioned that it changes the physical order of the file. But i couldn't find an article or a video or anything in my textbook which mentioned whether it'll use a separate file after the reordering. If i found it, why would i be asking here? – sumanth Sep 29 '20 at 05:14
  • Postgres doesn't have clustered indexes –  Sep 29 '20 at 05:23

2 Answers2

1

Clustered index in the way you explaining is reordering the data in the database file itself.

In the 90's this was widely used in dbase, but it did not help very much after you build it. Bad example is - collegue of mine reordered a table so id 1 as on first row, id 2 on second, id 42 was on row 42. Finally he was able to access the table as array.

This was supported in Informix.

This is also supported in MySQL MyISAM, but there command is - 'alter table x order by name'. However in MySQL, database does not take advantage of the reordering. Only thing is - if you do 'select * ftom x where city=5', results will be ordered by name (notice there are no order by in the SQL).

Note clustered index exists until you insert some data into database.

So Answer - No it does not have additional space (file). However I don't help as much as books suggest.

Nick
  • 9,962
  • 4
  • 42
  • 80
0

PostgreSQL does not have a clustered index, so the question is moot.

PostgreSQL does have a CLUSTER command, but that rewrites a table (and its indexes) so that the table is physically sorted in index order afterwards. The index by which the table is cluatered will not be different from other indexes afterwards, it just provided the ordering.

This physical table order is not preserved, and subsequent table modifications let the ordering rot, so that you will have to run another CLUSTER after a while. An additional disadvantage is that the table is lockes to concurrent reads while CLUSTER is running.

You cluster a table to speed up index range scans: if adjacent index entries point to the same table block, you will have substantially less I/O, and additionally, that I/O tends to be sequential.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • How is it different from a sorted file? – sumanth Sep 29 '20 at 06:34
  • When you say "it", do you mean the table or the index? The index is *always* sorted (although it is organized as a tree, not a flat file). The table is only sorted right after `CLUSTER` has run. Subsequent modifications destroy the sorting. – Laurenz Albe Sep 29 '20 at 06:37