如何调优Oracle SQL查询

I want to ask whether it better to use Synonym or the actual name of the table to tune the performance of a SQL query?

我想问一下,使用同义词或表的实际名称来调优SQL查询的性能是否更好?

1 个解决方案

#1

A synonym is a label to an actual database object. They are used as a layer of abstraction. The main use is for referencing objects in other schemas or remote databases. Using a synonym means we don't have to change out code when we move in different environments.

同义词是实际数据库对象的标签。它们被用作抽象层。主要用于引用其他模式或远程数据库中的对象。使用同义词意味着当我们在不同的环境中移动时,我们不必更改代码。

The performance hit of using a synonym is the look up. Given an object name in a query the optimizer will look for matching objects in this order:

使用同义词的性能影响是查找。给定查询中的对象名称,优化器将按以下顺序查找匹配的对象:

a table or view in the referenced schema (default is current schema)

引用模式中的表或视图(默认为当前模式)

a private synonym in the referenced schema

引用模式中的私有同义词

a public synonym

公共同义词

So, if our current schema has a table T23 the database will use that in our query rather than the table referenced by public synonym T23.

因此,如果我们当前的模式有一个表T23,数据库将在我们的查询中使用它,而不是公共同义词T23引用的表。

Depending on the state of the data dictionary the cost of this look up should range from utterly completely negligible to more-or-less negligible.

根据数据字典的状态,这种查找的成本应该从完全可以忽略不计到可忽略不计。

Using a synonym will not affect the performance of the actual query. That is, these two queries will have the same execution profile:

使用同义词不会影响实际查询的性能。也就是说,这两个查询将具有相同的执行配置文件:

select * from synonym_of_some_table;
select * from other_user.some_table@remote_db;

"even it the data in the table is very large" ?

“即使它表中的数据非常大”?

Yes, even if the data in the table is very large. Using a synonym won't affect retrieving the data either way. Synonyms are not like views, which can make tuning a lot harder.

是的,即使表中的数据非常大。使用同义词不会影响以任何方式检索数据。同义词不像视图,这可以使调整更加困难。

#1