Oracle base 12 Object objects synonyms/sequence/try/index

--Create synonyms
Create public synonym employees for hr.employees; --Common synonyms
Create public synonym permissions Required

All users of the table are granted public permissions
Grant Select on employees to public;

Create synonym t1_s for T1; --Private synonyms
If you want to refer to the same table in different contexts by different aliases, create a private synonym.

Compiling synonyms
alter synonym t1_s compile;

Managing synonyms
Select table_name, Synonym_name
From dba_synonyms
where owner= ' IKKI ';

Delete synonyms
Drop public synonym employees;
drop synonym t1_s;

--Create a sequence
Create sequence SEQ1
Increment by 1
Start with 1
MaxValue 100 | MinValue 10 | Nomaxvalue
Cache | NoCache
Cycle | Nocycle;

For example:
Create sequence SEQ1
Increment by 1
Start with 10
MaxValue 999
NoCache
Nocycle;

View sequence
SELECT * from User_sequences;
Select Seq1.nextval from dual;
Select Seq1.currval from dual;

Using sequences
CREATE TABLE EMP2 (
ID Number (10),
Name VARCHAR2 (20),
Dept VARCHAR2 (20),
BH Number (10)
);
Insert into EMP2 (bh,id,name,dept)
VALUES (seq1.nextval,302, ' Tom ', 2002);

--rowid
Select Rowid,bh,id,name
from EMP2;

SELECT * FROM EMP2
where rowid= ' Aaasovaaeaaaakoaab ';

--Create an attempt to
CREATE View My_employees as
Select Employee_id,first_name,last_name,salary
From Employees
where manager_id=122
with Read only; --users can only from the attempt to query

Delete attempts to
Drop View my_employees;

Materialization attempts to
The following things can be done on materialized attempts:
Creating an index on materialized attempts;
Create a materialized attempt on a partitioned table;
Attempts to partition the materialization.

Refresh Mode
On commit: In this way, when the change data of a primary table is committed, materialized attempts are automatically refreshed to reflect changes in the data.
On demand: In this way, you must perform such things as Dbms_mview. Refrest such a process to update materialized attempts.

Refresh Type
Complete: This refresh type will completely recalculate the query based on the materialized attempt. If materialization attempts to build for 12 hours, then rebuilding will take the same amount of time. Obviously, this option should not be used when a small number of rows in the primary table are changed, deleted, or inserted.
Fast:oracle will use the materialized attempt log to record all changes to the main table. It then uses the materialized attempt log to update the materialized attempt. The materialized attempt log is a table based on the corresponding materialized attempt. Each table in the materialized attempt to relate to a join needs to have its own materialized attempt log in order to capture the table's changes.
Force: If you select this option, Oracle will use the fast refresh mechanism as much as possible, and if this mechanism is not available for some reason, the complete Refresh method will be used. This option defaults to the Refresh method.
Never: This refresh option does not refresh materialized attempts. Obviously, this is not a viable option for materialized attempts that have many changes to the main table.

Using the Dbms_mview Package

Create materialization attempt
1, grant the necessary permissions
Grant create materialized view to Ikki;
Grant query rewrite to Ikki;

2. Create materialized attempt log
Create materialized View Log
On products with Sequence,rowid
(prod_id, Prod_name, Prod_desc, Prod_subcategory,
Prod_subcategory_desc, Prod_category, Prod_category_desc, Prod_weight_class, Prod_unit_of_measure, Prod_pack_size, SUPPLIER_ID,
Prod_status, Prod_list_price, Prod_min_price)
including new values;

Create materialized view Log on sales
With Sequence,rowid
(prod_id, cust_id, time_id, channel_id, promo_id,
Quantity_sold, Amount_sold)
including new values;

3. Create materialization attempt
Create materialized View PRODUCT_SALES_MV
Build Immediate
Refresh Fast
Enable query rewrite
As select P.prod_name, sum (s.amount_sold) as Dollar_sales,
COUNT (*) as CNT, COUNT (s.amount_sold) as Cnt_amt
From sales s, Products p
where s.prod_id=p.prod_id
Group BY P.prod_name;

Build immediate: Create materialized attempt immediately, this is the default option. Another approach is to use the build deferred option, which loads the materialized attempt and its data at a later specified time.
Refresh fast: Description materialization attempts to use the fast Refresh method.
Enable query rewrite: Indicates that the Oracle optimizer will transparently rewrite the query to use the newly created materialized attempt instead of the underlying primary table.

Sql> create materialized view Log on YGB;
Materialized view log created.

Sql> Create materialized View MV_YGB
2 Refresh Fast
3 AS SELECT * from YGB;
Materialized view created.

Delete materialized view logs
sql> drop materialized view Log on YGB;
Materialized view Log dropped.

Delete materialized views
Sql> drop materialized view MV_YGB;
Materialized view dropped.

--Create an index
Create INDEX emp_tb_id on EMP2 (ID);

Delete Index
Drop index emp_tb_id;

Rebuilding indexes
Alter index EMP_TB_ID rebuild;

Alter index EMP_TB_ID rebuild online;

When an online rebuild is performed on an index, all DML operations can be performed, but no DDL operations are performed.

Non-visible Index
You can use a non-visible index as a temporary index to perform certain special operations, or to test the index before it becomes a formal index. In addition, sometimes making an index invisible is visible instead of dropping the index or making it unusable. You can test the effect of deleting this index by making an index temporarily invisible.

1. Create an invisible Index
CREATE INDEX idx_test on test (tname)
Invisible

2. Change the index to make it invisible
Alter index idx_test Invisible;
Alter index idx_test visible;

3. Whether the query index is visible
Select Index_name,visibility from User_indexes
where index_name= ' idx_test ';

--Guidelines for creating valid indexes for Oracle tables:
1. You can use an index if the data you need to access does not exceed 4% or 5% in the table. A full table scan applies to queries that have a higher percentage of the data requested than the full table. Keep in mind that retrieving rows with an index requires two read operations: Read the index and read the table.
2. A relatively small table should avoid using indexes. A full table scan works well for small tables. For small tables, you do not need to store data for both the table's data and indexes.
3. Create a primary key for all tables. When you specify a column as the primary key, Oracle automatically creates an index of that column.
4. Index the columns that are used in the multi-table join operation.
5. Index the columns that are used frequently in the WHERE clause.
6. Index the columns included in the order by and group by operations, or in other operations such as union and distinct that involve sorting. Because the indexes are already sorted, the ordering requirements for performing the aforementioned operations are significantly reduced.
7. A column consisting of a long string is not usually the candidate column for the index.
8. Columns that are frequently changed are not indexed in theory because of the cost implications.
9, so that the number of indexes is small.
10. You need to use a composite index when the unique column value may not be unique. In a composite index, the driver column or the first column should be the most selective column.
Golden rule for indexing: The table's index should be based on the table columns that appear in the query you are locking in. A table can create multiple indexes: You can choose to create an X-column or y-column or a combined index of both.

Oracle base 12 Object objects synonyms/sequence/try/index