Tag Info

New answers tagged

11

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must ...


0

In SQL Server all index keys must internally be unique. This is required to obtain lock keys that address exactly one row. It is also required for index maintenance. Imagine an NCI on a column that only has one value in it (100% duplicates). If a row is deleted from the table the storage engine must find the corresponding NCI row and delete it as well. If ...


1

there is another way to reduce your bloat space when you can remove tuples in heap pages, this method use less adding space to reduce your bloat table. LIKE exp : pg93@db-172-16-3-150-> psql psql (9.3.3) Type "help" for help. digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp); CREATE TABLE digoal=# insert into t_bloat ...


1

If your environment has disk space more than this table's "real data(after reduced)" size. you can use pg_reorg reduce this table's bloat space. or you can use londiste3 increment copy this table, and use less time to exchange. but if your env has no more space than this table, you can't reduce.


3

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. Create a new tablespace on the other disk. ...


0

A primary key will enforce uniqueness in your data, which is almost always highly desired. One of the issues you'll run into if you allow yourself to have multiple copies of the same record is that any results you have will be skewed to the point of being unusable for many purposes. Also, this will happen, no matter how much you try to say it won't. You ...


0

You may believe primary key is unnecessary, but in my experience it is critical. You may never use it. But the database itself uses the primary key to determine PHYSICAL organization of the data. Correction: I have been corrected here - in SQL Server, the physical organization is based on the clustering index. By default a primary key IS the clustering ...


1

There is some space used for the upper levels of the B-tree (how much depends on how large the table is). However if you look at a Clustered index and a Non Clustered index of the same data (exact same keys and including all columns of the table in the non clustered index) the upper levels of the B-tree would look very similar (if not the same). The ...



Top 50 recent answers are included