Egalitarian's questions

Tenho uma estrutura de tabela da seguinte forma:

CREATE TABLE `sale_product_inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tier_number` int(11) NOT NULL DEFAULT '1',
  `sale_product_pool_id` int(11) DEFAULT NULL,
  `inventory` int(11) NOT NULL,
  `in_cart_units` int(11) DEFAULT '0',
  `size_display_order` tinyint(4) NOT NULL DEFAULT '0',
  `last_updated_by` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`sale_id`,`product_id`,`tier_number`,`size`,`sale_product_pool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

OBS: Tenho um Index UNIQUE = sale_id, product_id, tier_number, size,sale_product_pool_id

Quando eu executo esta consulta:

select * from sale_product_inventories 
where 
sale_id in (502,504)  and 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql usa o índice Unique e o tempo de execução é de 0,7 milissegundos

MAS

quando executo esta consulta

select * from sale_product_inventories 
where 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

O MySql não usa o índice UNIQUE e o tempo de execução é de 76 milissegundos.

Mysql: 5.5.27 Versão InnoDB: 1.1.8

Minha pergunta é por que o mysql está se comportando dessa maneira. Alguém por favor pode me ajudar com isso.

EDIT:
me deparei com isso, então pensei que poderia ser útil adicionar MySQL geralmente não pode usar índices em colunas, a menos que as colunas sejam isoladas na consulta. “Isolar” a coluna significa que ela não deve fazer parte de uma expressão ou estar dentro de uma função na consulta.

Deram-me um problema para resolver, no qual existe uma tabela chamada Cenários no Master Db que contém os detalhes de todo o Tablespace para o qual tenho que encontrar o tamanho. O O/P deve conter o tamanho da tabela (realmente consumido) e o tamanho do índice e o número de linhas.

Então, escrevi um script de dimensionamento (PL/SQL) para encontrar o tamanho de todo o Table Space naquele servidor de banco de dados específico.

Mas estou recebendo essa exceção específica depois que ela é executada por dias.

ORA-01555: snapshot muito antigo: segmento de rollback número 9 com nome "_SYSSMU9$" muito pequeno

Não tenho certeza do que pode estar causando isso, pois o tamanho dos dados não é tão grande.

estou anexando o script

    SET SERVEROUTPUT ON size '10000000'
declare
TYPE cur_typ IS REF CURSOR;
a_Temp number := 0;
x_Total number := 0;
i number := 0;
c_cursor cur_typ;
query_str varchar2(500);
num_long Long;
currentScenarioDB nvarchar2(255);
tableExists number := 0;
scenarioId varchar2(50);
scenarioName varchar2(100);
dbIdentifier nvarchar2(50);
queryToFindScenarioNameAndId varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
selectQuery varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
insertStatement varchar2(2000) := 'Insert Into ScenarioTableAndIndexSize  values (:1,:2,:3,:4,:5,:6,:7) ';
-- scenarioId,scenarioname,,dbIdentifier,tablename,dataSize,IndexSize,rowNumber
tableIndexSize number := 0;
numOfRows number := 0;
rowNum number := 0;
tableDataSize number := 0;
Cursor getScenarioDb is select dbidentifier from scenarios where dbidentifier IN (select Distinct(TABLESPACE_NAME) from dba_tables);
begin
DBMS_OUTPUT.ENABLE(10000000);
execute immediate 'truncate table ScenarioTableAndIndexSize';
open getScenarioDb;
fetch getScenarioDb into currentScenarioDB;
while getScenarioDb%found
loop
queryToFindScenarioNameAndId := selectQuery || '''' || currentScenarioDB || '''';
execute immediate queryToFindScenarioNameAndId  into scenarioId,scenarioName;
              declare
              queryToFindNoofRows varchar2(1000);
        queryConstruct varchar2(32767) := '';
        outputTableInScenarioDb nvarchar2(256);
        Cursor getTablesInScenario is select DISTINCT TABLE_NAME from dba_tables where owner =  currentScenarioDB and TABLE_NAME not like 'BIN%' and table_name != 'SCENARIOTABLEANDINDEXSIZE' order by table_name;
        begin
              tableExists := 0;
        open getTablesInScenario;
        fetch getTablesInScenario into outputTableInScenarioDb;
        while getTablesInScenario%found
        loop
              queryConstruct  := 'select nvl( sum (';
              tableIndexSize  := 0;
              tableDataSize := 0;
              numOfRows := 0;
              queryToFindNoofRows := 'select count(*) from  '||  currentScenarioDB || '.' ||outputTableInScenarioDb;
              execute immediate queryToFindNoofRows into numOfRows;
              if numOfRows > 0 then
---------------------------Beginning Of Section to find Table data Size------------------------------------------------------------------------------------------------
                  declare
                      Cursor getColumnsInTables is select * from dba_tab_columns where Table_Name = outputTableInScenarioDb and owner = currentScenarioDB;
                      dbaTabColumnRow dba_tab_columns%rowtype;
                      dataType varchar2(40);
                      fields varchar2(1000);
                      begin
                      open getColumnsInTables;
                      fetch getColumnsInTables Into dbaTabColumnRow;
                      while getColumnsInTables%found
                      loop
                      dataType := dbaTabColumnRow.DATA_TYPE;
                     if dataType = 'CLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH(' || dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'BLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH('|| dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'LONG' then
                        fields := 'nvl(VSIZE(''''),0)';
                        x_Total := 0;
                        query_str := 'SELECT  ' || dbaTabColumnRow.COLUMN_NAME || '  FROM  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;
                                      OPEN c_cursor FOR query_str;
                                  LOOP
                                  FETCH c_cursor INTO num_long;
                                  EXIT WHEN c_cursor%NOTFOUND;
                             a_Temp:=length(num_long);
                             x_Total:= x_Total + a_Temp;
                                  END LOOP;
                           CLOSE c_cursor;
                     else
                        fields := 'nvl(vsize(' || dbaTabColumnRow.COLUMN_NAME || '),0)';
                     end if;
                           fetch getColumnsInTables Into dbaTabColumnRow;
                         if getColumnsInTables%found then
                       queryConstruct := queryConstruct || fields||'+';
                     else
                     queryConstruct := queryConstruct || fields;
                     end if;
                      end loop;
                      end;
                                      queryConstruct := queryConstruct || '),0) as sizeOfTable from  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;            
                                      --dbms_output.put_line(queryConstruct);
                                      execute immediate queryConstruct into tableDataSize;
---------------------------End Of Section to find Table data Size-------------------------------------------------------------------------------------------------------------

                      ---------------Section To find index size
                          declare
                Index_Name nvarchar2(4000);
                sql_statement varchar2(1000) := 'select nvl(USED_SPACE,0) from index_stats';
                stat1 varchar2(1000) := 'analyze index ';
                stat2 varchar2(1000) := '  validate structure';
                stat3 varchar2(2000) := '';
                size1 number := 0;
                cursor indexOnTable is select INDEX_NAME from dba_indexes where tablespace_name = currentScenarioDB and  table_name = outputTableInScenarioDb and index_type = 'NORMAL';
                    begin
                    open indexOnTable;
                    fetch indexOnTable into Index_Name;
                    while indexOnTable%found
                    loop
                      stat3 := stat1 || currentScenarioDB ||'.' ||Index_Name || stat2;
                      execute immediate stat3;
                      execute immediate  sql_statement into size1;
                      tableIndexSize := tableIndexSize + size1;
                    fetch indexOnTable into Index_Name;
                    end loop;
                    close indexOnTable;
            end;
                      -----end of section to find index size
              else
                rowNum := rowNum + 1;
              end if;
                            tableDataSize := x_Total + tableDataSize;
              execute immediate insertStatement using   scenarioId,scenarioName,currentScenarioDB,outputTableInScenarioDb,tableDataSize,tableIndexSize,numOfRows;
                               x_Total := 0;
              fetch getTablesInScenario into outputTableInScenarioDb;
        end loop;
        end;
fetch getScenarioDb into currentScenarioDB;
end loop;
close getScenarioDb;
end;

O tamanho da tabela é encontrado desta forma:

1. Se o campo for do tipo Lob então para calcular seu tamanho eu uso nvl(DBMS_LOB.GETLENGTH(),0)
2. Se o campo for do tipo Long, então eu faço um loop sobre todos os valores Long e encontro seu tamanho
   usando a função Length() incorporada
3. Se o campo for de qualquer outro tipo, uso nvl(vsize(),0) Apenas para especificar que o usuário tem permissões em todos os bancos de dados

E então eu resumo todos eles para encontrar o tamanho total dos dados na tabela.

Alguém pode me dizer o que estou fazendo de errado ou o que devo fazer para corrigir o erro?

Obrigado.

Escrevi um script PL/SQL para encontrar o tamanho de uma longa coluna em uma tabela. Apenas para tornar o script genérico, estou passando o nome da tabela e o nome da coluna como variáveis, mas estou recebendo um erro dizendo que a tabela ou exibição não existe. os detalhes são:

ORA-06550: line 8, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

O roteiro é:

declare
a number := 0;
x number := 0;
i number := 0;
tablename varchar2(100):= 'FILES';
columnname varchar2(100):= 'FILESIZE';
begin
for  j in (select columnname from tablename) loop
  a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(j.columnname));
    i := i+1;
dbms_output.put_line(i);
  x:= x + a;
end loop;
dbms_output.put_line(x);
end;

O nome da tabela é FILES. E o nome da coluna é FILESIZES.

Você pode sugerir o que estou fazendo de errado. E o que posso fazer para encontrar o tamanho da coluna longa?

Obrigado.

Estou tentando executar um bloco PL/Sql usando o OracleClientProvider em .Net. A linguagem que estou usando é c#, o DB é oracle10g

O que estou realmente fazendo é o seguinte:

  //ConnectionSting is the connection String
  OracleConnection connection = new OracleConnection(connectionString);
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = connection;

  // Open the connection
  connection.Open();

  //queryFile contains the PL/SQL Script I am trying to execute;
  String queryFile = ConfigurationManager.AppSettings["MasterDbScript"];
  String dataInFile = new StreamReader(queryFile).ReadToEnd();
  cmd.CommandText = dataInFile;

  connection.Open();
  cmd.ExecuteNonQuery(); 
  //close the connection
  connection.close();

O bloco PL/SQL executa corretamente quando eu o executo pelo cliente ORACLE, mas aqui ele lança um erro ORA-00922: opção ausente ou inválida

Gostaria de perguntar: 1. Os scripts são executados de forma diferente da consulta normal? 2. O que estou fazendo de errado?

Sugestões/Respostas

Obrigado.