How to Get Information About an Index and Table Owner in Oracle

How do I get information about an index and table owner in Oracle?

According to the docs, you can just do:

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

or

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

if you want all indexes...

Oracle - How to obtain information on index fields etc

How can I list indexes columns readily defined in Oracle

SELECT *
FROM all_ind_columns
WHERE table_name = 'YOUR_TABLE'

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532

How do I use those indexes in a select statement

You don't have to do anything. If the index will speed up the query, Oracle will use it automatically.

How to show indexes in Oracle SQL

If you have the privileges, you can use the ALL_INDEXES or USER_INDEXES views. The query would be:

SELECT  *
FROM    all_indexes
WHERE   table_name = 'COUNTRY';

If you want some information on the columns included in the index, you can select those from ALL_IND_COLUMNS. Documentation regarding these views can be found here Static Data Dictionary Views: ALL_ALL_TABLES to ALL_MVIEWS

how to find all indexes and their columns for tables, views and synonyms in oracle

Your query should work for synonyms as well as the tables. However, you seem to expect indexes on views where there are not. Maybe is it materialized views ?

How To Check for an Index in Oracle

select count(*) from user_indexes where index_name = 'myIndex'

sqlplus won't support IF..., though, so you'll have to use anonymous PL/SQL blocks, which means EXECUTE IMMEDIATE to do DDL.

DECLARE
    i INTEGER;
BEGIN
    SELECT COUNT(*) INTO i FROM user_indexes WHERE index_name = 'MYINDEX';
    IF i = 0 THEN
        EXECUTE IMMEDIATE 'CREATE INDEX myIndex ...';
    END IF;
END;
/

Edit: as pointed out, Oracle stores unquoted object names in all uppercase.

What uniquely identifies an index in Oracle?

An index is uniquely identifed by its INDEX_NAME and its OWNER (ie the schema it belongs to). Basically, each object in a schema must have a unique name. Two different tables cannot have an index of the same name, unless these indexes belong to two different schemas.

Consider this simple example:

create table mytable(id int, val int);
create index myindex on mytable(id);
-- ok

create table mytable2(id int);
create index myindex on mytable2(id);
-- ORA-00955: name is already used by an existing object

Oracle:How to check index status?

EXPLAIN PLAN will show you what index is used and other information.

For example:

explain plan for
select * from tablename where createdate>=to_date('2016-02-29','yyyy-mm-dd');

select * from table(dbms_xplan.display);

Plan hash value: 3955337982

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLENAME |     1 |     9 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CREATEDATE">=TO_DATE(' 2016-02-29 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

---

Related Topics