A table is having few rows, should you create indexes on this table

A table is having few rows, should you create indexes on this table
by

1 Answer

Answer :

Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to  large.  
You can create an index on any column; however, if the column is not used in any of these situations, creating an  index on the column does not increase performance and the index takes up resources unnecessarily.
by

Related questions

On which columns you should create Indexes?

Description : On which columns you should create Indexes?

Last Answer : In general, you should create an index on a column in any of the following situations: ▪ The column is queried frequently ▪ A referential integrity constraint exists on the column ▪ A UNIQUE key ... ORDER BY clauses ▪ On columns that have few of the same values or unique values in the table

How you will avoid your query from using indexes?

Description : How you will avoid your query from using indexes?

Last Answer : SELECT * FROM emp Where emp_no+' '=12345; i.e you have to concatenate the column name with space within codes in the where condition. SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234; i.e using HINTS

What is the use of INDEXES option in IMP command ?

Description : What is the use of INDEXES option in IMP command ?

Last Answer : A flag to indicate whether import should import index on tables or not.

What is the use of INDEXES option in EXP command ?

Description : What is the use of INDEXES option in EXP command ?

Last Answer : A flag to indicate whether indexes on tables will be exported.

Can you built indexes online?

Description : Can you built indexes online?

Last Answer : YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the ... online index build operations: CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

When should you rebuilt indexes?

Description : When should you rebuilt indexes?

Last Answer : In 90% cases - NEVER. When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based. Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.

What type of indexes are available in Oracle?

Description : What type of indexes are available in Oracle?

Last Answer : There are many index types within Oracle: B*Tree Indexes - common indexes in Oracle. They are similar construct to a binary tree; they provide fast access by key, to an individual row or range of ... They allow data to be sorted from big to small (descending) instead of small to big (ascending).

Deleting the Duplicate rows in the table

Description : Deleting the Duplicate rows in the table

Last Answer : We can delete the duplicate rows in the table by using the Rowid

How do you find the numbert of rows in a Table ?

Description : How do you find the numbert of rows in a Table ?

Last Answer : A bad answer is count them (SELECT COUNT(*) FROM table_name) A good answer is :- 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g ... utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

How will you delete duplicating rows from a base table?

Description : How will you delete duplicating rows from a base table?

Last Answer : delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or delete duplicate_values_field_name dv from table_name ta where rowid

If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

Description : If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?

Last Answer : It won't, Because SYSDATE format contains time attached with it.

What is the built-in routine used to count the no of rows in a group?

Description : What is the built-in routine used to count the no of rows in a group?

Last Answer : Get_group _row_count System Variables

What is the use of ROWS option in IMP command ?

Description : What is the use of ROWS option in IMP command ?

Last Answer : A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.

What is the use of ROWS option in EXP command ?

Description : What is the use of ROWS option in EXP command ?

Last Answer : Flag to indicate whether export should compress fragmented segments into single extents.

Which parameter in Storage clause will reduce no. of rows per block?

Description : Which parameter in Storage clause will reduce no. of rows per block?

Last Answer : PCTFREE parameter Row size also reduces no of rows per block.

What are built-ins used for Processing rows?

Description : What are built-ins used for Processing rows?

Last Answer : GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function) RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure)

What are the built-ins used for processing rows?

Description : What are the built-ins used for processing rows?

Last Answer : Get_group_row_count(function) Get_group_selection_count(function) Get_group_selection(function) Reset_group_selection(procedure) Set_group_selection(procedure) Unset_group_selection(procedure)

Consider the following two statements : (a) Data scrubling is a process to upgrade the quality of data, before it is moved into Data warehouse. (b) Data scrubling is a process of rejecting data from data warehouse to create indexes. Which one of the following options is correct ? (A) (a) is true, (b) is false (B) (a) is false, (b) is true (C) Both (a) and (b) are false (D) Both (a) and (b) are true

Description : Consider the following two statements : (a) Data scrubling is a process to upgrade the quality of data, before it is moved into Data warehouse. (b) Data scrubling is a process of rejecting data from data warehouse to create ... true (C) Both (a) and (b) are false (D) Both (a) and (b) are true

Last Answer : (A) (a) is true, (b) is false

A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Description : A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Last Answer : Disable the foreign key constraint to the parent, drop the table, re- create the table, enable the foreign key constraint.

An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?

Description : An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?

Last Answer : No.

A Column is having many repeated values which type of index you should create on this column, if you have to?

Description : A Column is having many repeated values which type of index you should create on this column, if you have to?

Last Answer : For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves, and a b tree index would be fairly useless in a database of 20 million vehicles.

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Description : Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Last Answer : SELECT customer_no, payments from customer C1 WHERE 3

A protective plantation in a certain area, against strong winds. It is usually comprised of a few rows of trees (or shrubs)

Description : A protective plantation in a certain area, against strong winds. It is usually comprised of a few rows of trees (or shrubs)

Last Answer : Ans. Wind breaks

How are book indexes made?

Description : How are book indexes made?

Last Answer : answer:There are many options for generating an index. Paid software like Cindex for ~$500, services that do it for you for $100-200, or free software. You can download this free indexer PDF Index ... need to read through and make sure it gets what you want and reports only the important key words.

Which grouping do you work with when formatting text in word? 1) Tables, paragraphs and indexes 2) Paragraphs, indexes and sections 3) Characters, sections and paragraphs 4) Indexes, characters and tables 5) None of these

Description : Which grouping do you work with when formatting text in word? 1) Tables, paragraphs and indexes 2) Paragraphs, indexes and sections 3) Characters, sections and paragraphs 4) Indexes, characters and tables 5) None of these

Last Answer : Answer :1

You organize files by storing them in a) Archives b) Folders c) Indexes d) Lists

Description : You organize files by storing them in a) Archives b) Folders c) Indexes d) Lists

Last Answer : b) Folders

You organise files by storing them in a) Archives b) Folders c) Indexes d) Lists e) None of these

Description : You organise files by storing them in a) Archives b) Folders c) Indexes d) Lists e) None of these

Last Answer : b) Folders

The Journal first published in 1889, featured the Jones 'Average', the first of several indexes of stock and bond prices on the New York Stock Exchange. How do we know that today?

Description : The Journal first published in 1889, featured the Jones 'Average', the first of several indexes of stock and bond prices on the New York Stock Exchange. How do we know that today?

Last Answer : Answer: Wall Street Journal

Data which improves the performance and accessibility of the database are called: (A) Indexes (B) User Data (C) Application Metadata (D) Data Dictionary

Description : Data which improves the performance and accessibility of the database are called: (A) Indexes (B) User Data (C) Application Metadata (D) Data Dictionary

Last Answer : (A) Indexes

How does one create a standby database? (for DBA)

Description : How does one create a standby database? (for DBA)

Last Answer : While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!! On your standby database, issue ... the standby database, stop the recovery process and activate it: ALTER DATABASE ACTIVATE STANDBY DATABASE;

Name a tablespace automatically created when you create a database.

Description : Name a tablespace automatically created when you create a database.

Last Answer : The SYSTEM tablespace.

What command would you use to create a backup control file?

Description : What command would you use to create a backup control file?

Last Answer : Alter database backup control file to trace.

Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?

Description : Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?

Last Answer : All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

How does one create and drop database users?

Description : How does one create and drop database users?

Last Answer : Look at these examples: CREATE USER scott IDENTIFIED BY tiger -- Assign password DEFAULT TABLESACE tools -- Assign space for table and index segments TEMPORARY TABLESPACE temp; -- Assign sort space ... give the user some space quota on its tablespaces: ALTER USER scott QUOTA UNLIMITED ON tools;

What utility is used to create a physical backup?

Description : What utility is used to create a physical backup?

Last Answer : Either rman or alter tablespace begin backup will do..

The following describes means to create a OEM V1.x (very old!!!) repository on WindowsNT:

Description : The following describes means to create a OEM V1.x (very old!!!) repository on WindowsNT:

Last Answer : Create a tablespace that would hold the repository data. A size between 200- 250 MB would be ideal. Let us call it Dummy_Space. . Create an Oracle user who would own this repository. Assign ... would trigger the creation of the repository. From now on, Enterprise manager is ready to accept jobs.

How does one create a repository? (for DBA

Description : How does one create a repository? (for DBA

Last Answer : For OEM v2 and above, start the Oracle Enterprise Manager Configuration Assistant (emca on Unix) to create and configure the management server and repository. Remember to setup a backup for the repository database after creating it.

What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?

Description : What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?

Last Answer : Add_parameter Create_Parameter_list Delete_parameter Destroy_parameter_list Get_parameter_attr Get_parameter_list set_parameter_attr

Can we create two blocks with the same name in form 3.0 ?

Description : Can we create two blocks with the same name in form 3.0 ?

Last Answer : No

How will you create multiple rollback segments in a database ?

Description : How will you create multiple rollback segments in a database ?

Last Answer : Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in the SYSTEM tablespace. Make new rollback segment ... ) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.

How does one create a RMAN recovery catalog? (for DBA

Description : How does one create a RMAN recovery catalog? (for DBA

Last Answer : Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example: sqlplus sys SQL>create user ... . Look at this example: rman catalog rman/rman target backdba/backdba RMAN> register database;

Why is it preferable to create a fewer no. of queries in the data model?

Description : Why is it preferable to create a fewer no. of queries in the data model?

Last Answer : Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.

How does one create a password file? (for DBA

Description : How does one create a password file? (for DBA

Last Answer : The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be ... visible in the process table of many systems. Administrators needs to be aware of this!

How do you create a new session while open a new form?

Description : How do you create a new session while open a new form?

Last Answer : Using open_form built-in setting the session option Ex. Open_form('Stocks ',active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False

How does one create a new database? (for DBA)

Description : How does one create a new database? (for DBA)

Last Answer : One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal ... ='/u02/oradata/'; ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/'; CREATE DATABASE;

Which default Database roles are created when you create a Database?

Description : Which default Database roles are created when you create a Database?

Last Answer : CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. The following query lists all the roles in ... ------------ -------- CONNECT NO RESOURCE NO DBA NO SECURITY_ADMIN YES

What is the minimum number of instances you need to have in order to create a RAC?

Description : What is the minimum number of instances you need to have in order to create a RAC?

Last Answer : 1. You can create a RAC with just one server.

Can one export a subset of a table? (for DBA

Description : Can one export a subset of a table? (for DBA

Last Answer : From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\"where deptno=10\"

How can you gather statistics on a table?

Description : How can you gather statistics on a table?

Last Answer : The ANALYZE command.

How would you determine who has added a row to a table?

Description : How would you determine who has added a row to a table?

Last Answer : Turn on fine grain auditing for the table.