Как обезопасить себя в кризис?
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд

Table access full

Fix unnecessary full-table scans

Oracle Database Tips by Donald BurlesonJanuary 29, 2015

While not all large-table full-table scans are problematic, a large-table full-table scan is a common symptom of a SQL execution problem. Large-table full-table scans in an explain plan (TABLE ACCESS FULL) should always be examined to verify that it is not due to a database problem, such as a missing index.

Unnecessary large-table full-table scans «full-table scans» (FTS) are an important symptom of sub-optimal SQL and cause unnecessary I/O that can drag down an entire database.

The first step in validating a full-table scan is to evaluate the SQL based on the number of rows returned by the query and your own guess about the number of trips to the data blocks that would be needed to fetch the rows.

Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.

This decision is partly based on the index key value described by clustering_factor in the dba_indexes view. However, it is not a simple process.

The choice of a full table scan vs. index access as the «best» access plan for a SQL statement depends on many factors. The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.

These factors are listed in their order of importance:

  • Sub-optimal optimizer_mode:The all_rows mode minimizes computing resources and favors full-table scans, while the first_rows_n mode favors index access.
  • Missing indexes: Missing indexes, (especially missing function-based indexes) will cause in unnecessary large-table full-table scans.
  • Bad CBO statistics: Bad/stale optimizer statistics (dbms_stats) will cause Oracle to mis-judge the execution plan for a SQL query.
  • Missing CPU and disk statistics: Missing system statistics (dbms_stats.gather_system_stats) will prevent the optimizer from making a smart decision about invoking multi-block I/O.
  • Histograms: For skewed data columns, missing column histograms will influence the choice of index vs. full-table scan access.
  • Index clustering: The physical row order on the data blocks is important to know when choosing an index, and the clustering of the table rows to the desired index (as displayed in dba_indexes clustering factor).
  • Sub-optimal setting for db_file_multiblock_read_count: The optimization of db_file_multiblock_read_count has been automated in 10g release 2 and beyond to prevent errors.
  • Sub-optimal setting for optimizer_index_cost_adj: This is a throttle that makes index access more attractive to the optimizer, and lowering the value of optimizer_index_cost_adj will cause the SQL optimizer to favor indexes. WARNING — DO NOT adjust this parameter until you have examined other «root cause» issues like bad metadata statistics.
  • Sub-optimal setting for optimizer_index_caching: The optimizer_index_caching parameter is set by the DBA to help the optimizer know, on average, how much of an index resides inside the data buffer. The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100.
  • Parallel query: Turning on Oracle parallel query (OPQ) will tell the optimizer that full-scans are less expensive, and it may change the execution plans for hundreds of SQL statements. If you are on an SMP server with dozens of CPU’s a full-table scan can be parallelized for faster execution. However, you should never read a data block unless it is needed to get rows for your SQL result set. Remember, parallel query only effects full-table scans, and it has no effect when performing index access.
  • Block Size: The number of rows that resides on each data block influences the choice between a full-table scan of an index scan. This is especially true when you are using RAID with a smaller stripe size, and reading a table with a 2k block size would take significantly more I/O and reading a table with a 32k block size.

The most common tuning tool for addressing unnecessary full-table scans is the addition of indexes, especially function-based indexes.

The decision about removing a full-table scan should be based on a careful examination of the amount of logical I/O (consistent gets) of the index scan versus the costs of the full-table scan.

This decision should be made while factoring in the multi-block reads and possible parallel full-table scan execution. In some cases, an unnecessary full-table scan can be forced to use an index. This is done by adding an index hint to the SQL statement.

If you are looking to enhance your Oracle tuning skills, you may enjoy Rampant TechPress — Oracle Tuning: The Definitive Reference by Donald K. Burleson , with over 900 pages of BC’s favorite tuning tips & scripts.

You can buy it directly from the publisher and get instant access to the code depot of Oracle tuning scripts.


I’m trying to speed up my query which selects only 30 records out of 3million records. All my conditions are indexed but when I explains it, it says TABLE ACCESS FULL. I use «between» and, it seemingly, when I reduce range of «between» then it does INDEX RANGE SCAN. Is there anyway I can force to do INDEX RANGE SCAN? Thanks. -Jay.

Thread Tools
  • Linear Mode
  • Switch to Hybrid Mode
  • Switch to Threaded Mode


I’m trying to speed up my query which selects only 30 records out of
3million records.
All my conditions are indexed but when I explains it, it says TABLE ACCESS
I use «between» and, it seemingly, when I reduce range of «between» then it

Is there anyway I can force to do INDEX RANGE SCAN?


post your oracle version and query. Also, what are
optimizer_index_caching and optimizer_index_cost_adj set to?
Have you yzed the relevant tables/indexes?


select /*+ index(table_name index_name */ rest of your query .

«Jay» wrote in message news: .


> Is there anyway I can force to do INDEX RANGE SCAN?

1- Use an index hint. Build an index on the columns in youtr WHERE
clause and add a hint to the query.

2 — Set optimizer_index_cost_adj=10

3 — Add a rule hint. Rule will always use an index.


The view that a Full Table Scan (FTS) is a bad thing.. is nothing but
an OWT (old wifes’ tale).

If the cost of using indexes is bigger than using a FTS, then Oracle
will use a FTS.

Exactly. And there you have stated WHY there is a difference in
execution plan. Pity that you did not spend some time thinking this
through as you would have arrived at the answer.

Simple scenario. I have a 3GB index. I do a select with a BETWEEN

Case 1. The value range specified in the between clause covers 2GB of
data in that index (e.g. WHERE surname BETWEEN ‘A’ AND ‘RZ’)

Case 2. The value range specified covers 10MB of index data (e.g.

Now look at this from the work that the database needs to do.

In case 1, the db looks at this as says «hmm.. you are covering 80% of
all index values. That means that 80% or even more of the data in the
table will satisfy the criteria.»

Thus the database, being the clever bugger it is (unlike what many
seems to think), decides NOT to use the index.

80% of the data in the table will match the criteria. So instead of
reading 80% of the index followed by reading 80% of the table, it
rather reads 100% of the table and not use the index at all. (80%
being an example — the actual percentage value is indexes & tables

Q. what is the most expensive operation in a database?
A. I/O

Case 2. The exact opposite of case 1. Thus the index IS used.

You have proved two things.
a) Oracle CBO works as it should (and better than you thought)
b) You could spend some more time with the Oracle manuals 🙂

IMO never ever enforce an index range scan (yes it is possible using
hints). Index range scans can be a lot more painful than a FTS. When
dealing with unknown index ranges, let the database (i.e. CBO)


«Billy Verreynne» wrote in message

Читать еще:  Как импортировать данные в access

Like he said: he’s doing case 2 (30 rows out of 3 million)
and the optimizer is NOT picking the index.
In the chapter about indexes in Expert oneonone, TK goes
at length under which conditions using an index is or is
not better than doing a FTS. With examples. Worth a look.

Maybe not in his case?

TK’s book for one helps in these situations.

In anyone’s book that is not an unknown index range.
I agree though: WHEN dealing with *unknown* ranges, then
it’s better to let the CBO do its work. If it tends to err
on the side of pessimism, it’s always possible to
nudge it in the right direction with the «optimizer_*»
stuff. That is of course the general case.

OT: waddyareckon: Boks, AllBlacks or the Poms?

Nuno Souto


Can you post the query and the explain? I can think of a number of reasons
why your index might not be being used, but you don’t give sufficient
information as to why.

Niall Litchfield
Oracle DBA
Audit Commission Uk
«Jay» wrote in message


Yeah, I did see that «30 rows being returned/selected», but I was
thinking group by was neglected to be mentioned.

Well either that, or the CBO is seriously y when deciding FTS to
do only 30 rows. The latter I will only believe when hard evidence is
given. 🙂

Yep. Total coincidence. The Book is right here next to the keyboard.

Just a pity that he did not add to the myths that FTSs are not evil.
I’ve seen a lot of frustration and tuning by people who sees a FTS in
an execution plan and immediately go «holy crap!» and then start
swinging away at the problem with index hints. Instead the warning
bells should go as a FTS implies that you want to hit big volumes with
your SQL.

Agree. That is IF the statement on 30 rows being selected ALSO implies
that these are also *only* 30 rows (of the millions) being processed.
And in the absense of more detail, I cannot see that to be the case
when the CBO decides to do a FTS.

Tough one. Of course I’m hoping for the Boks. The Poms are damn good,
but the longer you’re on the top, the more difficult it becomes to
stay there. I do not think that they can keep up that level of play
consistently for such a long time. They have everything to lose and
little to gain. Not forgetting the Kiwis either. They have a good
team. And then there’s the mightly AllBlacks. So the heart says
Bokke but the brain says mebbe the AllBlacks.

Besides, us southern hemisphere okes have to stand together. 🙂


«Billy Verreynne» wrote in message
> Yeah, I did see that «30 rows being returned/selected», but I was
> thinking group by was neglected to be mentioned.
> Well either that, or the CBO is seriously y when deciding FTS to
> do only 30 rows. The latter I will only believe when hard evidence is
> given. :-)[/ref]

One of the scenarios I suspect is that the data is seriously skewed and
there are no histogram stats. (Or alternatively bind variables are being
used in a skewed case). 30 Rows out of 3m have a status of ‘UNKNOWN’ but as
the table only has 3 distinct values for status ‘PROCESSED’,’UNPROCESSED’
and ‘UNKNOWN’ the optimizer thinks that it will pick a 3rd of the table with
the condition AND STATUS=’UNKNOWN’.

> Tough one. Of course I’m hoping for the Boks. The Poms are damn good,
> but the longer you’re on the top, the more difficult it becomes to
> stay there. I do not think that they can keep up that level of play
> consistently for such a long time. They have everything to lose and
> little to gain. Not forgetting the Kiwis either. They have a good
> team. And then there’s the mightly AllBlacks. So the heart says
> Bokke but the brain says mebbe the AllBlacks.
> Besides, us southern hemisphere okes have to stand together. :-)[/ref]

yeah that’s what I saw in SA all the SA guys I met standing shoulder to
shoulder with the Aussies 🙁 FWIW I see England winning the competition in a
tough fight with NZ in the final. And then collapsing for the next 10 years.

And another thing I fly 6000 miles to SA and there’s no bloody southern
cross or any constellation at all to see in the night sky. Talk about a let
down. sheesh.

Niall Litchfield
Oracle DBA
Audit Commission UK
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

Таблицы файлов в SQL Server 2012

Традиционным способом хранения в БД неструктурированных двоичных файлов, таких как видео, аудио, картинки и т. п. является определение в таблице поля типа varbinary(max) или применение FILESTREAM, появившегося в SQL Server 2008. В этой статье будет рассмотрен новый подход с использованием таблиц FileTable, которые появились в SQL Server 2012. Технология FileTable основывается на функционале FileStream, при этом расширяя его возможности. В FileTable для организации структуры каталогов используется тип HierarchyId, о котором вы можете прочитать в моей предыдущей статье.

Настройка сервера для поддержки FileTable

Для корректной работы FileTable сначала необходимо произвести настройку SQL Server. В первую очередь нужно включить поддержку FileStream на уровне сервера. Это можно сделать через SQL Server Configuration Manager. Перейдите в меню
Пуск > Все программы > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager. Далее в левой панели выберите SQL Server Services, при этом в правой панели откроются все службы SQL Server. Выберите экземпляр SQL Server, на котором нужно включить поддержку FileStream, и перейдите в «Свойства» (рис. 1).

Рис. 1. Выбор экземпляра SQL Server, на котором необходимо включить поддержку FileStream

В окне свойств перейдите на вкладку FILESTREAM. Чтобы включить поддержку FileStream на сервере, отметьте флаг «Enable FILESTREAM for Transact-SQL access». Если вы хотите иметь возможность доступа к FileStream из файловой системы Windows, то отметьте флаг «Enable FILESTREAM for file I/O streaming access». Также здесь вы можете задать имя общей папки Windows для данных FileStream. Если нужно, чтобы удалённые клиенты могли иметь доступ к данным FileSteam, хранящимся в общей папке, то отметьте флаг «Allow remote clients to have streaming access to FILESTREAM data» (рис. 2). Все эти настройки можно также произвести при установке SQL Server.

Рис. 2. Включение поддержки FILESTREAM в SQL Server Configuration Manager

Далее откройте SQL Server Management Studio, зайдите в свойства сервера, перейдите на вкладку Advanced и в поле FileStream Access Level выберите «Full access enabled», как показано на рис. 3.

Рис. 3. Включение поддержки FILESTREAM в SQL Server Management Studio

Это также можно сделать с помощью скрипта, приведённого ниже:

Далее необходимо создать или модифицировать базу данных, которая будет поддерживать FileStream. Для этого в свойствах базы данных перейдите на вкладку Filegroups и создайте файловую группу согласно рис. 4.

Рис. 4. Создание файловой группы для поддержки FileStream

Затем перейдите на вкладку General и создайте файл, как показано на рис. 5. Тип данных установите в значение «FILESTREAM Data». В поле Filegroup выберите ранее созданную файловую группу. Также в поле Path задайте путь для хранения данных FileStream, например, c:FileTableDemoData . Этот путь должен существовать в файловой системе.

Рис. 5. Создание файла базы данных для поддержки FileStream

Теперь перейдите на вкладку Options. В разделе FILESTREAM в поле FILESTREAM Directory Name задайте имя директории. Эта директория будет создана в общей папке сервера, которая была задана при включении FileStream, а в неё, в свою очередь, будут помещаться папки для каждой таблицы FileTable, созданной в этой базе данных. Также, если вы хотите иметь доступ к файлам, хранящимся в FileTables, из файловой системы, необходимо установить нетранзакционный доступ, выбрав в поле FILESTREAM Non-Transacted Access значение «Full» (рис. 6).

Рис. 6. Настройка нетранзакционного доступа и имени директории для FileTables

Все эти действия можно сделать с помощью следующего скрипта:

Создание таблицы FileTable

Таблица FileTable является специализированной пользовательской таблицей с заранее заданной и фиксированной схемой, поэтому при её создании нет необходимости указывать список столбцов. Синтаксис для создания такой таблицы выглядит следующим образом:

FILETABLE_DIRECTORY — это корневой каталог для всех файлов и каталогов, хранящихся в FileTable. Если при создании не указано имя каталога, то в качестве него используется имя самой таблицы.
FILETABLE_COLLATE_FILENAME указывает имя параметров сортировки, применяемых к столбцу Name в таблице FileTable. Если значение не указано или задано как database_default, столбец унаследует параметры сортировки текущей базы данных.
С учётом вышесказанного, синтаксис создания FileTable может быть упрощён до следующего:

Читать еще:  User access manager

После создания FileTable перейдите к базе данных, раскройте узел FileTables и затем раскройте список столбцов созданной таблицы. Вы увидите структуру, показанную на рис. 7.

Рис. 7. Структура таблицы FileTable

В таблице хранится большинство атрибутов файла, а также его имя, размер, тип и родительский католог. Для указания каталогов существует флаг is_directory. В таблицы такого типа нельзя добавлять новые пользовательские столбцы, а также удалять или изменять существующие, но можно создавать пользовательские индексы, триггеры или ограничения.
После проделанных операций вы можете посмотреть структуру папок, которые создал SQL Server. В директории c:FileTableDemoData была создана папка FileTableDemo_FileStream. Все данные FileStream будут сохраняться в этой папке. Сейчас, если вы перейдёте в неё, то увидите там файл filestream.hdr и две директории (рис. 8). Они нужны для корректной работы FileStream и не должны удаляться или модифицироваться вручную.

Рис. 8. Папка, созданная SQL Server для хранения данных FileStream

Также вы можете перейти в общую сетевую папку, созданную SQL Server. В обобщённом виде путь к этой папке выглядит следующим образом: SERVERNAMEFILESTREAM_WINDOWS_SHARE_NAMEFILESTREAM_TABLE_NAME FILETABLE_DIRECTORY .
В нашем случае это будет выглядеть так: ServerSqlServer2012ExpFileTableDemoFiles .
В эту директорию можно попасть и с помощью SQL Server Management Studio. Для этого кликните правой кнопкой на созданной таблице FileTable и выберите пункт Explore FileTable Directory (рис. 9) Для доступа к директории FileTable требуются соответствующие разрешения SQL Server для таблицы. Если у пользователя нет таких разрешений, то доступ к этой папке из файловой системы будет запрещен.

Рис. 9. Доступ к папке FileTable из SQL Server Management Studio

В настоящий момент эта директория пуста. Вы можете помещать в неё файлы и папки обычным копированием, как показано на рис. 10.

Рис. 10. Копирование файлов в папку FileTable через файловую систему

Теперь сделаем выборку из нашей таблицы с помощью обычного скрипта:

Получим следующий результат, показанный на рис. 11.

Рис. 11. Выборка файлов из таблицы FileTable

Как и следовало ожидать, наш файл появился в таблице со всеми необходимыми атрибутами.
Также данные в таблицу можно добавлять, обновлять и удалять с помощью обычных инструкций INSERT, UPDATE и DELETE языка Transact-SQL.

Рабта с каталогами и путями в таблицах FileTable

Для работы с каталогами и путями таблиц FileTables существуют 3 функции:

  • FileTableRootPath([ ], [ ]) — получает корневой путь для конкретной таблицы FileTable или для текущей базы данных. Первый параметр — это имя таблицы FileTable, для которой необходимо вернуть путь. Он является необязательным, значение по умолчанию — это текущая база данных. Второй параметр — это целочисленное выражение, определяющее способ форматирования серверных компонентов пути. Использование этой функции приведено на рис. 12.

Рис. 12. Пример функции FileTableRootPath

  • .GetFileNamespacePath([ ], [ ]) — возвращает путь к файлу или каталогу в таблице FileTable. — это имя столбца file_stream типа varbinary(max) в таблице FileTable. — это целочисленное выражение, указывающее, какой путь возвращать — относительный или абсолютный. Он является необязательным, и по умолчанию выводится относительный путь внутри каталога уровня базы данных. Параметр аналогичен одноимённому параметру в пункте выше. Использование этой функции приведено на рис. 13.

Рис. 13. Пример функции GetFileNamespacePath

  • GetPathLocator( ) — возвращает значение идентификатора path_locator для заданного файла или каталога в таблице FileTable. — это путь к пространству имен в FileTable. Он имеет тип nvarchar(max). Использование этой функции приведено на рис. 14.

Рис. 14. Пример функции GetPathLocator

Преимущества использования FileTable

Особенность FileTables заключается в том, что эта технология объединяет компонент SQL Server Database Engine с файловой системой NTFS, размещая данные больших двоичных объектов (BLOB) типа varbinary(max) в файловой системе. Исходя из этого, можно выделить следующие преимущества:

  • возможен нетранзакционный доступ через файловую систему Windows, при этом производительность равняется производительности файловой системы;
  • для кэширования файлов в хранилище FileTable используется системный кэш NT, при этом SQL-буфер используется только для обработки запросов;
  • размер двоичного файла ограничен только размером NTFS-раздела;
  • возможен также транзакционный доступ с помощью обычных инструкций SELECT, INSERT, UPDATE и DELETE;
  • доступно использование интегрированных служб SQL Server, таких как резервное копирование, встроенная поддержка безопасности, полнотекстовый поиск и т. д.

Introduction to tables

Tables are essential objects in a database because they hold all the information or data. For example, a database for a business can have a Contacts table that stores the names of their suppliers, e-mail addresses, and telephone numbers. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other objects. Before you create tables, consider your requirements and determine all the tables that you might need. For an introduction to planning and designing a database, see Database design basics.

In this article


A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.

A record: Contains specific data, like information about a particular employee or a product.

A field: Contains data about one aspect of the table subject, such as first name or e-mail address.

A field value: Each record has a field value. For example, Contoso, Ltd. or .

Table and field properties

Tables and fields also have properties that you can set to control their characteristics or behavior.

1. Table properties

2. Field properties

In an Access database, table properties are attributes of a table that affect the appearance or behavior of the table as a whole. Table properties are set in the table’s property sheet, in Design view. For example, you can set a table’s Default View property to specify how the table is displayed by default.

A field property applies to a particular field in a table and defines one of the field’s characteristics or an aspect of the field’s behavior. You can set some field properties in Datasheet view. You can also set any field property in Design view by using the Field Properties pane.

Data types

Every field has a data type. A field’s data type indicates the kind of data that the field stores, such as large amounts of text or attached files.

A data type is a field property, but it differs from other field properties as follows:

You set a field’s data type in the table design grid, not in the Field Properties pane.

A field’s data type determines what other properties the field has.

You must set a field’s data type when you create the field.

You can create a new field in Access by entering data in a new column in Datasheet view. When you create a field by entering data in Datasheet view, Access automatically assigns a data type for the field, based on the value that you enter. If no other data type is implied by your input, Access sets the data type to Text. If needed, you can change the data type by using the Ribbon.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

Access creates a field with a data type of:

You can use any valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.

Number, Long Integer

Number, Long Integer

The date and time formats recognized are those of your user locale.

December 31, 2016

The currency symbol recognized is that of your user locale.

Table relationships

Although each table stores data about a different subject, tables in an Access database usually store data about subjects that are related to each other. For example, a database might contain:

A customers table that lists your company’s customers and their addresses.

A products table that lists the products that you sell, including prices and pictures for each item.

An orders table that tracks customer orders.

Because you store data about different subjects in separate tables, you need some way to tie the data together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specifies fields that the tables have in common. For more information, see Guide to table relationships.

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

Primary key A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people. For more information, see adding or changing a table’s primary key.

Читать еще:  Remote access citigroup

Foreign key A table can also have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order’s customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

1. A primary key, identified by the key icon next to the field name.

2. A foreign key — note the absence of the key icon.

Do not add a field if you expect that each unique entity represented in the table might require more than value for the field. Continuing the preceding example, if you want to start tracking orders placed by your customers, you do not add a field to the table, because each customer will have more than one order. Instead, you create a new table to store orders, and then create a relationship between the two tables.

Benefits of using relationships

Keeping data separated in related tables produces the following benefits:

Consistency Because each item of data is recorded only once, in one table, there is less opportunity for ambiguity or inconsistency. For example, you store a customer’s name only once, in a table about customers, rather than storing it repeatedly (and potentially inconsistently) in a table that contains order data.

Efficiency Recording data in only one place means you use less disk space. Moreover, smaller tables tend to provide data more quickly than larger tables. Finally, if you don’t use separate tables for separate subjects, you will introduce null values (the absence of data) and redundancy into your tables, both of which can waste space and impede performance.

Comprehensibility The design of a database is easier to understand if the subjects are properly separated into tables.

Plan your tables with relationships in mind. You can use the Lookup Wizard to create a foreign key field if the table that contains the corresponding primary key already exists. The Lookup Wizard creates the relationship for you. For more information, see Create or delete a lookup field.

Oracle — what can be done to remove Full Table Scan/Table Access Full?

Oracle SQL which needs to be tuned:

Predicate Information (identified by operation id):

No. of rows in the 3 tables used in the query :

(ma_ce_en_in table is culprit. Primary key on columns MCEI_SID and MCEI_SID_SEQ. No other index on this table.)

Question 1 — I want to tune the query so I don’t want Full Table Scan(or Table Access Full) on ma_ce_en_in as this is the only big table. What can be done to remove Full Table Scan from this table?

Question 2 — The above query is present in a function, how to find how many times the function runs in a week?

I’m not very sure what is FTS? – Ilya Bursov 09 июн. 17 2017-06-09 20:41:48

It’s full table scan OR table access full. Edited my question too. – swetabh malaviya 09 июн. 17 2017-06-09 20:51:51

Apparently Oracle is completely under estimating the number of rows in ‘ma_ce_en_in’. Are your statistics up to date? – a_horse_with_no_name 09 июн. 17 2017-06-09 20:53:07

That scalar sub-select in the ‘where’ clause seems strange (if not useless). You are comparing the column ‘mcei.business_dt’ to itself. Did you mean that to be a join condition instead? e.e.g ‘JOIN MA_CE_EN_IN mcei ON mce.mce_sid = mcei.mce_sid and mcei.business_dt = . ‘? – a_horse_with_no_name 09 июн. 17 2017-06-09 20:58:02

Yes I have gathered the statistics and the above counts of the 3 tables are exact count. – swetabh malaviya 09 июн. 17 2017-06-09 20:58:47

Could you show a definition of this fuction: ‘GE_CG_SI_FR_C_SN’ ? The full table scan probably is not a problem, but using this function can cause poor performance. – krokodilko 09 июн. 17 2017-06-09 21:01:53

That sub-select is tested and good. It has some other conditions too which are being evaluated. Also carefully see the table name, it is different but has similar name. My main aim is to remove Full Table Scan from MA_CE_EN_IN table.How can I do that? Also please help me on my second question. – swetabh malaviya 09 июн. 17 2017-06-09 21:04:19

krokodilko — code of the function is below. But I dont think it is the problem. please take a look. ‘SELECT cg_sid into out_cg_sid from code_generic cg where cg_sname = p_cg_sname and cgt_sname = p_cgt_sname and ((active_flag = ‘Y’ AND P_Ignore_Active_Flag = ‘N’) OR P_Ignore_Active_Flag = ‘Y’ ); return out_cg_sid;’ – swetabh malaviya 09 июн. 17 2017-06-09 21:07:57

Make an index then you will have seek not scan. – Hogan 09 июн. 17 2017-06-09 21:12:20

**Just me know what are the ways to remove Full Table Scan from that table? thanks** – swetabh malaviya 09 июн. 17 2017-06-09 21:13:42

@Hogan — ‘create index test on ma_ce_en_in(Latest_Full_Run_Flag,Mcei_State_Cg_Sid,Engine_Type_Cg_Sid);’ is this the best index or something else we can do? After creating the index, 3 Nested Loops have come in the explain plan, are they ok? or should we convert them to hash join, if yes how? – swetabh malaviya 09 июн. 17 2017-06-09 21:24:31

2 ответа

In one of the comments, way down below the post (instead of IN THE POST where it should be), you state that you have an index on LATEST_FULL_RUN_FLAG, MCEI_STATE_CG_SID and ENGINE_TYPE_CG_SID.

How many rows have the FLAG set to ‘Y’? (You can run a simple query to find out.) If the answer is «half the rows» or even «15% of the rows», the optimizer will likely choose not to use the index. (If only 1% of the rows had the flag set to ‘Y’ then the index should be used.)

If that is so — are either the STATE_CG_SID or the ENGINE_TYPE_CG_SID more selective? For example, each possible value appears in no more than 1% of the rows? If so, you should put the most selective of the columns FIRST in the multi-column index; the only way the optimizer can use the index is if the value for the FIRST column in the index is very selective. Otherwise you will end up with a full table scan.

Создан 09 июн. 17 2017-06-09 22:09:12 mathguy

thanks all and @mathguy – swetabh malaviya 12 июн. 17 2017-06-12 09:46:09

Question 2 — The above query is present in a function, how to find how many times the function runs in a week?

Answer — You cannot find the no. of times a function has been executed. But you can find how many times its sql has been executed.

a) To find the no. of execution, you can check AWR but it could be possible your sql is NOT picked up by the awr. You can execute below to get stats of all sql in awr.

b) However, you may not be allowed to run the above code into prod. So the next option is to check oracle views.

Find the SQL_ID from its text

select executions,v.* from v$sql v where sql_text like ‘%select * from ABCtable%’;—90771ja3rt3rw

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт